﻿CREATE FUNCTION [dbo].[GetZone]
(
	@CarrierID int, 
	@LocationID int,
	@CountryID int,
	@PostalCode nvarchar(12)
)
RETURNS char(2) as
begin
	declare @zone char(2)

	select top 1 @zone = Zone
	from tblCarrierZonePostalCode
	where CarrierID = @CarrierID
		and LocationID = @LocationID
		and CountryID = @CountryID
		and PostalCodeLow is null or (
			LEFT(@PostalCode, len(PostalCodeLow)) between PostalCodeLow and PostalCodeHi )
	order by coalesce(PostalCodeLow, ' ') desc

	return @zone
end
